import sqlite3
from pymysql.converters import escape_string


class SQLiteHelper:
    # 实测关掉自动提交和磁盘同步，1000条数据写入0.0319秒
    def __init__(self, db_name, auto_commit=True, synchronous=None):
        def dict_factory(cursor, row):
            d = {}
            for idx, col in enumerate(cursor.description):
                d[col[0]] = row[idx]
            return d

        self.db_name = db_name
        self.auto_commit = auto_commit
        self._conn = sqlite3.connect(db_name, check_same_thread=False)
        self._conn.row_factory = dict_factory
        self._cursor = self._conn.cursor()
        if synchronous:
            self._cursor.execute('PRAGMA synchronous = {};'.format(synchronous))  # FULL NORMAL OFF

    def create_table(self, table_name, ddl, index=(), constraint=None):
        """
        sqlite字段类型
            integer
            text
            real
            blob
        创建示例
        CREATE TABLE "t1" (
          "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
          "name" text(20),
          "age" integer(3),
          "balance" real(10,2)
        );
        :param table_name:
        :param ddl:
        :param index
        :param constraint
        :return:
        """
        fields = ''
        for d in ddl:
            fields += '"{}" {},'.format(d['field'], d['type'])
        if constraint:
            fields += '{},'.format(constraint)
        sql_str = 'CREATE TABLE IF NOT EXISTS "{table_name}" ({fields});'.format(table_name=table_name, fields=fields[0:-1])
        self._cursor.execute(sql_str)
        try:
            for idx in index:
                self._cursor.execute(f'CREATE INDEX "{idx["name"]}" ON "{table_name}" ("{idx["field"]}");')
        except Exception as e:
            pass

    def commit(self):
        return self._conn.commit()

    def query(self, sql):
        self._cursor.execute(sql)
        if self.auto_commit:
            self._conn.commit()  # 为了提升写入速度，建义在所有的语句准备完成后再commit
        return self._cursor

    def find(self, table_name, condition='1', order=None, field='*'):
        sql_str = 'SELECT {field} FROM {table_name} WHERE {condition}'.format(field=field, table_name=table_name, condition=condition)
        if order:
            sql_str += ' ORDER BY {order}'.format(order=order)
        sql_str += ' LIMIT 1'
        self._cursor.execute(sql_str)
        return self._cursor.fetchone()

    def findAll(self, table_name, condition, field='*', order=None, limit=None):
        sql_str = 'SELECT {field} FROM {table_name} WHERE {condition}'.format(field=field, table_name=table_name, condition=condition)
        if order:
            sql_str += ' ORDER BY {order}'.format(order=order)
        if limit:
            sql_str += ' LIMIT {limit}'.format(limit=limit)
        self._cursor.execute(sql_str)
        return self._cursor.fetchall()

    def insert(self, table_name, data):
        field, value = '', ''  # 字段,数据值
        for (k, v) in data.items():
            field += k.rstrip() + ','
            if v is None:
                value += 'null,'
            else:
                value += '"{value}",'.format(value=escape_string(str(v).replace("'", '').replace('"', '')))
        sql_str = 'INSERT INTO {table_name} ({fields}) VALUES({values});'.format(table_name=table_name, fields=field[0:-1], values=value[0:-1])
        self.query(sql_str)
        return self._cursor.rowcount  # 返回影响的数据条数

    def update(self, table_name, data, condition):
        sql_str = 'UPDATE {table_name} SET '.format(table_name=table_name)
        for (k, v) in data.items():
            if v is None:
                sql_str += '{k} = null,'.format(k=k)
            else:
                sql_str += '{k} = "{v}",'.format(k=k, v=escape_string(str(v)))
        sql_str = '{sql_str} WHERE {condition};'.format(sql_str=sql_str[0: -1], condition=condition)
        self.query(sql_str)
        return self._cursor.rowcount  # 返回影响的数据条数

    def delete(self, table_name, condition):
        sql_str = 'DELETE FROM {table_name} WHERE {condition}'.format(table_name=table_name, condition=condition)
        self.query(sql_str)
        return self._cursor.rowcount  # 返回影响的数据条数

    def count(self, table_name, condition='1', field='1'):
        sql_str = 'SELECT count({field}) as num FROM {table_name} WHERE {condition}'.format(field=field, table_name=table_name, condition=condition)
        self._cursor.execute(sql_str)
        return self._cursor.fetchone().get('num')


if '__main__' == __name__:
    _ddl = [
        {'field': 'id', 'type': 'INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT'},
        {'field': 'name', 'type': 'text(20)'},
        {'field': 'age', 'type': 'integer(3)'},
        {'field': 'balance', 'type': 'real(10,2) NOT NULL DEFAULT 0'},
    ]
    test = SQLiteHelper('../test.db', auto_commit=False, synchronous='OFF')
    test.create_table('crate_table_test', _ddl)
    for i in range(1000):
        test.insert('crate_table_test', {'name': '小明', 'age': '12'})
    test.commit()
    print(test.findAll('crate_table_test', '1', order='id DESC'))
    print(test.delete('crate_table_test', '1'))
    test.commit()
